﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

namespace JumbotCms.WebFile
{
    public partial class Test : System.Web.UI.Page
    {
        private string ConStr = "server=(local);database=KnowFar2009;uid=KnowFar20090414;pwd=KnowFar20090414;";
        private SqlConnection sqlCon = null;
        private SqlCommand sqlComm = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            Proc2();
        }
        #region 修改密码
        /// <summary>
        /// <![CDATA[
        /*
        * 存储过程  ---先执行该存储过程
          create procedure [proc_ChangePass]
              @UserName varchar(32) ,--用户名
              @OldPwd varchar(64) ,--旧密码
              @NewPwd varchar(64) ,--新密码
              @OutPut int output--输出参数,状态的判断
          as 
              if exists ( select Id ,UserName from [jcms_normal_user] where UserName = @UserName )--用户存在,修改密码的条件
              begin
                  --获取用户旧密码
                  declare @_oldPwd varchar(64)
                  set @_oldPwd = (select UserPass from [jcms_normal_user] where UserName = @UserName)
                   --判断用户旧密码
                  if ( @OldPwd = @_oldPwd ) 
                  begin
                      update [jcms_normal_user] set UserPass = @NewPwd where UserName = @UserName
                      set @OutPut = 1--旧密码正确.修改成功
                  end
                  else
                  begin
                      set @OutPut = 2--用户存在,密码不正确,修改失败
                  end
              end
              else 
              begin
                  set @OutPut = 3--用户不存在,无法修改
              end
          * 
          */
        /// ]]>
        /// </summary>
        private void Proc1()
        {
            using (sqlCon = new SqlConnection(ConStr))
            {
                sqlCon.Open();
                sqlComm = new SqlCommand("proc_ChangePass", sqlCon);
                //设置命令的类型为存储过程
                sqlComm.CommandType = CommandType.StoredProcedure;

                //设置参数
                sqlComm.Parameters.Add("@UserName", SqlDbType.VarChar, 32);
                sqlComm.Parameters.Add("@OldPwd", SqlDbType.VarChar, 64);
                sqlComm.Parameters.Add("@NewPwd", SqlDbType.VarChar, 64);
                sqlComm.Parameters.Add("@OutPut", SqlDbType.Int, 3);
                //设置参数的类型为输出参数,默认情况下是输入,
                sqlComm.Parameters["@OutPut"].Direction = ParameterDirection.Output;

                //为参数赋值
                sqlComm.Parameters["@UserName"].Value = "jumbot";
                sqlComm.Parameters["@OldPwd"].Value = "46789962fe139ce45b32f9dcf1633309";
                sqlComm.Parameters["@NewPwd"].Value = "4cbcb962fe139ce45b32f9dcf1633309";
                //执行
                sqlComm.ExecuteNonQuery();
                //得到输出参数的值,把赋值给name,注意,这里得到的是object类型的,要进行相应的类型轮换
                Response.Write(sqlComm.Parameters["@OutPut"].Value.ToString());

            }
        }
        #endregion
        #region 返回表的记录行数
        /// <summary>
        /// <![CDATA[
        /*
        * 存储过程  ---先执行该存储过程
        create proc proc_GetTableCount
        (
            @tablename varchar(300),
            @where varchar(3000),
            @rowcount int output
        )
        as
            declare @sql varchar(4000)
            set @sql = 'select count(*) from ' + @tablename
            if(len(@where) >0)
                begin
                    set @sql = @sql + ' where ' + @where
                end
            exec(@sql)
            set @rowcount=@@rowcount
                  * 
                  */
        /// ]]>
        /// </summary>
        private void Proc2()
        {
            using (sqlCon = new SqlConnection(ConStr))
            {
                sqlCon.Open();
                sqlComm = new SqlCommand("proc_GetTableCount", sqlCon);
                //设置命令的类型为存储过程
                sqlComm.CommandType = CommandType.StoredProcedure;

                //设置参数
                sqlComm.Parameters.Add("@tablename", SqlDbType.VarChar, 300);
                sqlComm.Parameters.Add("@where", SqlDbType.VarChar, 3000);
                sqlComm.Parameters.Add("@rowcount", SqlDbType.Int).Direction = ParameterDirection.Output;

                //为参数赋值
                sqlComm.Parameters["@tablename"].Value = "jcms_module_article";
                sqlComm.Parameters["@where"].Value = "id>40";
                //执行
                sqlComm.ExecuteNonQuery();
                Response.Write(sqlComm.Parameters["@rowcount"].Value.ToString());
                //Response.Write(sqlComm.ExecuteScalar().ToString());

            }
        }
        #endregion
    }
}
